﻿using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using MySql.Data.MySqlClient;
using LdExpress;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
namespace LdExpress
{
    public class Prices
    {
        public long ID = -1;
        public string DC20 = "";
        public string DC40 = "";
        public string HC40 = "";
        public string RF20 = "";
        public string RF40 = "";
        public string TSPort = "";
        public string Sailing = "";
        public string TT = "";
        public string Validity = "";
        public string ValidityEx = "";
        public string Import = "";
        public string Description = "";
        public string DescriptionEx = "";
        public long CarrID = -1;
        public string CarrName = "";
        public long PortToID = -1;//POL
        public long PortFromID = -1;//POD
        public string POLName = "";//POL
        public string PODName = "";//POD
        public DateTime LastUpdate = DateTime.Now;
        public int Sort = 0;
        public long UserID = 0;

        public string OT20 = "";
        public string OT40 = "";
        public string FL20 = "";
        public string FL40 = "";
        public string CONT45 = "";
        public string ValidityOTFL = "";
        public string DescriptionOTFL = "";

        public Prices()
        {
        }

        public Prices(long lID)
        {
            this.ID = lID;
        }

        public void Save(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            string strTableName = "tbl_Prices";

            if (ID <= 0) // new object, we insert new record to database
            {
                //ID = long.Parse(DBModule.GetNewID(typeof(Bill), "tbl_bill" , "ID", "ID", cn, trans));
                strSQL = "Insert into " + strTableName + " (" +
                             "`DC20`" +
                             ",`DC40`" +
                             ",`HC40`" +
                             ",`RF20`" +
                             ",`RF40`" +
                             ",`TSPort`" +
                             ",`Sailing`" +
                             ",`TT`" +
                             ",`Validity`" +
                             ",`ValidityEx`" +
                             ",`Import`" +
                             ",`Description`" +
                             ",`DescriptionEx`" +
                             ",`CarrID`" +
                             ",`PortToID`" +
                             ",`PortFromID`" +
                             ",`Sort`" +
                             ",`UserID`" +
                             ",`CarrName`" +
                             ",`LastUpdate`" +
                             ",`OT20`" +
                             ",`OT40`" +
                             ",`FL20`" +
                             ",`FL40`" +
                             ",`ValidityOTFL`" +
                             ",`DescriptionOTFL`" +
                             ",`CONT45`" +                             
                             ") " +
                         " values(" +
                             "'" + DBModule.RefineString(DC20) + "'" +
                             "," + "'" + DBModule.RefineString(DC40) + "'" +
                             "," + "'" + DBModule.RefineString(HC40) + "'" +
                             "," + "'" + DBModule.RefineString(RF20) + "'" +
                             "," + "'" + DBModule.RefineString(RF40) + "'" +
                             "," + "'" + DBModule.RefineString(TSPort) + "'" +
                             "," + "'" + DBModule.RefineString(Sailing) + "'" +
                             "," + "'" + DBModule.RefineString(TT) + "'" +
                             "," + "'" + DBModule.RefineString(Validity) + "'" +
                             "," + "'" + DBModule.RefineString(ValidityEx) + "'" +
                             "," + "'" + DBModule.RefineString(Import) + "'" +
                             "," + "'" + DBModule.RefineString(Description) + "'" +
                             "," + "'" + DBModule.RefineString(DescriptionEx) + "'" +
                             "," + CarrID.ToString() +
                             "," + PortToID.ToString() +
                             "," + PortFromID.ToString() +
                             "," + Sort.ToString() +
                             "," + UserID.ToString() +
                             "," + "'" + DBModule.RefineString(CarrName) + "'" +
                             "," + DBModule.RefineDatetime(LastUpdate) +
                             "," + "'" + DBModule.RefineString(OT20) + "'" +
                             "," + "'" + DBModule.RefineString(OT40) + "'" +
                             "," + "'" + DBModule.RefineString(FL20) + "'" +
                             "," + "'" + DBModule.RefineString(FL40) + "'" +
                             "," + "'" + DBModule.RefineString(ValidityOTFL) + "'" +
                             "," + "'" + DBModule.RefineString(DescriptionOTFL) + "'" +
                             "," + "'" + DBModule.RefineString(CONT45) + "'" +
                             ")";
                // run SQL statement
                //throw new Exception(strSQL);
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("prices", "add", cn, trans);                
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                strSQL = "Update " + strTableName + " set " +
                            "DC20='" + DBModule.RefineString(DC20) + "'" +
                             ",DC40='" + DBModule.RefineString(DC40) + "'" +
                             ",HC40='" + DBModule.RefineString(HC40) + "'" +
                             ",RF20='" + DBModule.RefineString(RF20) + "'" +
                             ",RF40='" + DBModule.RefineString(RF40) + "'" +
                             ",TSPort='" + DBModule.RefineString(TSPort) + "'" +
                             ",Sailing='" + DBModule.RefineString(Sailing) + "'" +
                             ",TT='" + DBModule.RefineString(TT) + "'" +
                             ",Validity='" + DBModule.RefineString(Validity) + "'" +
                              ",ValidityEx='" + DBModule.RefineString(ValidityEx) + "'" +
                             ",Import='" + DBModule.RefineString(Import) + "'" +
                             ",Description='" + DBModule.RefineString(Description) + "'" +
                             ",DescriptionEx='" + DBModule.RefineString(DescriptionEx) + "'" +
                             ",CarrID = " + CarrID.ToString() +
                             ",PortToID = " + PortToID.ToString() +
                             ",PortFromID = " + PortFromID.ToString() +
                             ",Sort = " + Sort.ToString() +
                             ",UserID = " + UserID.ToString() +
                             ",CarrName='" + DBModule.RefineString(CarrName) + "'" +
                             ",LastUpdate=" + DBModule.RefineDatetime(LastUpdate) + "" +
                             ",OT20='" + DBModule.RefineString(OT20) + "'" +
                             ",OT40='" + DBModule.RefineString(OT40) + "'" +
                             ",FL20='" + DBModule.RefineString(FL20) + "'" +
                             ",FL40='" + DBModule.RefineString(FL40) + "'" +
                             ",ValidityOTFL='" + DBModule.RefineString(ValidityOTFL) + "'" +
                             ",DescriptionOTFL='" + DBModule.RefineString(DescriptionOTFL) + "'" +
                             ",CONT45='" + DBModule.RefineString(CONT45) + "'" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(strSQL, cn, trans);
                UserObjectsLog.SaveObjectActionLog("prices", "edit", cn, trans);                
            }
        }
        public void Delete(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "DELETE FROM tbl_prices WHERE ID = " + this.ID.ToString();
            DBModule.ExecuteNonQuery(strSQL, cn, trans);
            UserObjectsLog.SaveObjectActionLog("prices", "delete", cn, trans);
        }
        public void Load(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT * FROM tbl_Prices WHERE ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("DC20")) DC20 = dr["DC20"].ToString();
                if (!dr.IsNull("DC40")) DC40 = dr["DC40"].ToString();
                if (!dr.IsNull("HC40")) HC40 = dr["HC40"].ToString();
                if (!dr.IsNull("RF20")) RF20 = dr["RF20"].ToString();
                if (!dr.IsNull("RF40")) RF40 = dr["RF40"].ToString();
                if (!dr.IsNull("TSPort")) TSPort = dr["TSPort"].ToString();
                if (!dr.IsNull("Sailing")) Sailing = dr["Sailing"].ToString();
                if (!dr.IsNull("TT")) TT = dr["TT"].ToString();
                if (!dr.IsNull("Validity")) Validity = dr["Validity"].ToString();
                if (!dr.IsNull("ValidityEx")) ValidityEx = dr["ValidityEx"].ToString();
                if (!dr.IsNull("Import")) Import = dr["Import"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("DescriptionEx")) DescriptionEx = dr["DescriptionEx"].ToString();
                if (!dr.IsNull("CarrID")) CarrID = long.Parse(dr["CarrID"].ToString());
                if (!dr.IsNull("PortToID")) PortToID = long.Parse(dr["PortToID"].ToString());
                if (!dr.IsNull("PortFromID")) PortFromID = long.Parse(dr["PortFromID"].ToString());
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("UserID")) UserID = int.Parse(dr["UserID"].ToString());
                if (!dr.IsNull("CarrName")) CarrName = dr["CarrName"].ToString();
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
                if (!dr.IsNull("OT20")) OT20 = dr["OT20"].ToString();
                if (!dr.IsNull("OT40")) OT40 = dr["OT40"].ToString();
                if (!dr.IsNull("FL20")) FL20 = dr["FL20"].ToString();
                if (!dr.IsNull("FL40")) FL40 = dr["FL40"].ToString();
                if (!dr.IsNull("CONT45")) CONT45 = dr["CONT45"].ToString();
                if (!dr.IsNull("ValidityOTFL")) ValidityOTFL = dr["ValidityOTFL"].ToString();
                if (!dr.IsNull("DescriptionOTFL")) DescriptionOTFL = dr["DescriptionOTFL"].ToString();
            }
        }
        public void LoadExtention(MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "SELECT tbl_Prices.*,POL.Name as POLName,POD.Name as PODName FROM tbl_Prices " +
            " LEFT JOIN tbl_ports POL ON POL.ID=tbl_Prices.PortToID " +
            " LEFT JOIN tbl_ports POD ON POD.ID=tbl_Prices.PortFromID " +
            " WHERE tbl_Prices.ID = " + ID.ToString() + " limit 0,1";

            DataSet ds = DBModule.ExecuteQuery(strSQL, cn, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID")) ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("DC20")) DC20 = dr["DC20"].ToString();
                if (!dr.IsNull("DC40")) DC40 = dr["DC40"].ToString();
                if (!dr.IsNull("HC40")) HC40 = dr["HC40"].ToString();
                if (!dr.IsNull("RF20")) RF20 = dr["RF20"].ToString();
                if (!dr.IsNull("RF40")) RF40 = dr["RF40"].ToString();
                if (!dr.IsNull("TSPort")) TSPort = dr["TSPort"].ToString();
                if (!dr.IsNull("Sailing")) Sailing = dr["Sailing"].ToString();
                if (!dr.IsNull("TT")) TT = dr["TT"].ToString();
                if (!dr.IsNull("Validity")) Validity = dr["Validity"].ToString();
                if (!dr.IsNull("ValidityEx")) ValidityEx = dr["ValidityEx"].ToString();
                if (!dr.IsNull("Import")) Import = dr["Import"].ToString();
                if (!dr.IsNull("Description")) Description = dr["Description"].ToString();
                if (!dr.IsNull("DescriptionEx")) DescriptionEx = dr["DescriptionEx"].ToString();
                if (!dr.IsNull("CarrID")) CarrID = long.Parse(dr["CarrID"].ToString());
                if (!dr.IsNull("PortToID")) PortToID = long.Parse(dr["PortToID"].ToString());
                if (!dr.IsNull("PortFromID")) PortFromID = long.Parse(dr["PortFromID"].ToString());
                if (!dr.IsNull("Sort")) Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("UserID")) UserID = int.Parse(dr["UserID"].ToString());
                if (!dr.IsNull("CarrName")) CarrName = dr["CarrName"].ToString();
                if (!dr.IsNull("POLName")) POLName = dr["POLName"].ToString();
                if (!dr.IsNull("PODName")) PODName = dr["PODName"].ToString();
                if (!dr.IsNull("LastUpdate")) LastUpdate = DateTime.Parse(dr["LastUpdate"].ToString());
                if (!dr.IsNull("OT20")) OT20 = dr["OT20"].ToString();
                if (!dr.IsNull("OT40")) OT40 = dr["OT40"].ToString();
                if (!dr.IsNull("FL20")) FL20 = dr["FL20"].ToString();
                if (!dr.IsNull("FL40")) FL40 = dr["FL40"].ToString();
                if (!dr.IsNull("CONT45")) CONT45 = dr["CONT45"].ToString();
                if (!dr.IsNull("ValidityOTFL")) ValidityOTFL = dr["ValidityOTFL"].ToString();
                if (!dr.IsNull("DescriptionOTFL")) DescriptionOTFL = dr["DescriptionOTFL"].ToString();
            }
        }
        public static void GetList(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select * from tbl_Prices where 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                if (strIDs != "All")
                    strSQL += strIDs;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }

        public static void GetListSerArea(string strIDs, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select tbl_Prices.*,POL.Name as POLNAME,Pod.Name as PODNAME, POL.Region as POLSER, POD.Region as PODSER" +
                ",POL.Country as POLCountry,Pod.Country as PODCountry" +
                " FROM tbl_Prices " +
                " LEFT JOIN tbl_ports POL ON POL.ID=tbl_Prices.PortToID " +
                " LEFT JOIN tbl_ports Pod ON Pod.ID=tbl_Prices.PortFromID " +
                " WHERE 1=1 ";
            if ((strIDs != "") && (strIDs != null))
            {
                if (strIDs != "All")
                    strSQL += strIDs;
            }
            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            //GV.Response.Write("sql=" + strSQL);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(strSQL, NumOfItemsPerPage, PageNo, out NumberOfPages, cn, trans);
            }
            else
                ds = DBModule.ExecuteQuery(strSQL, cn, trans);

        }
        public void DoSearch(string strKeywords, string OrderBy, out DataSet ds, MySqlConnection cn, MySqlTransaction trans)
        {
            string strSQL = "";
            ds = null;
            // build SQL statement
            strSQL = "Select `ID` from tbl_Prices where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                strSQL = strSQL + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(strSQL, cn, trans);
        }
    }
}
